-- =============================================
-- Stored procedure to delete Website table.
--
-- Modifications
-- 09/30/2003  E.Tatsui   Created
-- =============================================

CREATE      PROCEDURE amsp_CMDeleteWebsite
  @InWebsiteKey uniqueidentifier,
  @InContactID numeric
AS
BEGIN
  DECLARE
  @NavMenuID numeric,
  @ActiveContentCount numeric,
  @NavMenuLeft integer

  SELECT @ActiveContentCount = Count(*)
    FROM Nav_Menu a, vCurrent_Content b
   WHERE a.NavMenuID = b.NavMenuID
     AND a.WebsiteKey = @InWebsiteKey

  -- If there is active content, simply inactivate the Website.
  IF @ActiveContentCount = 0 BEGIN
    UPDATE Website
       SET ActiveFlag = 'N'
     WHERE WebsiteKey = @InWebsiteKey
  END
  ELSE BEGIN -- If there is no active content, go ahead and delete all the nav menus.
    DECLARE c_NMToDelete CURSOR FOR
     SELECT NavMenuID
       FROM Nav_Menu
      WHERE WebsiteKey = @InWebsiteKey
  
    OPEN c_NMToDelete
    FETCH NEXT FROM c_NMToDelete
     INTO @NavMenuID
  
    WHILE @@FETCH_STATUS = 0 BEGIN
      EXEC amsp_CMDeleteNavMenu @NavMenuID, @InContactID, 'Y', NULL
  
      FETCH NEXT FROM c_NMToDelete
       INTO @NavMenuID
    END
  
    CLOSE c_NMToDelete
    DEALLOCATE c_NMToDelete
    
    SELECT @NavMenuLeft = COUNT(*)
      FROM Nav_Menu
     WHERE WebsiteKey = @InWebsiteKey

    -- If we were successful in deleting all the navigation/folders, let's delete this website.
    IF @NavMenuLeft = 0 BEGIN
      DELETE FROM Website_Content_Authority WHERE WebsiteKey = @InWebsiteKey
      DELETE FROM Website_Security_Group WHERE WebsiteKey = @InWebsiteKey
      DELETE FROM Nav_Menu_Website WHERE WebsiteKey = @InWebsiteKey
      DELETE FROM Website WHERE WebsiteKey = @InWebsiteKey
    END
    ELSE BEGIN -- Otherwise, mark it as inactive.
      UPDATE Website
         SET ActiveFlag = 'N'
       WHERE WebsiteKey = @InWebsiteKey
    END
  END
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMDeleteWebsite] TO [IMIS]
GO